package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.EmployeeDTO;
import model.PublicHolidayDTO;
import dao.DAOException;
import dao.PublicHolidayDAO;

public class PublicHolidayDAOImpl implements PublicHolidayDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveappforiss";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";

	// Create onePublicHoliday instance	
	public PublicHolidayDTO createonePublicHoliday() {
        return new PublicHolidayDTO();
  }
	public PublicHolidayDTO findPublicHoliday(Date date) throws DAOException {
		String selectSql = "SELECT * FROM publicholiday WHERE Date='" + date
				+ "';";
		PublicHolidayDTO onePublicHoliday = loadPublicHoliday(selectSql);
		System.out.println(onePublicHoliday.toString());
		return onePublicHoliday;
	}
	
	private PublicHolidayDTO loadPublicHoliday(String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PublicHolidayDTO onePublicHoliday = createonePublicHoliday();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				   onePublicHoliday.setPublicHolidayID(result.getInt("PublicHolidayID")); 
	               onePublicHoliday.setName(result.getString("Name")); 
	               onePublicHoliday.setDate(result.getDate("Date")); 
	        	
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return onePublicHoliday;
	}
	
	public ArrayList<PublicHolidayDTO> findAllPublicHoliday() throws DAOException {
		String selectSql = "SELECT * FROM publicholiday;";
		ArrayList<PublicHolidayDTO>  items = loadAllPublicHoliday(selectSql);
		//System.out.println(onePublicHoliday.toString());
		return items;
	}
	
	private ArrayList<PublicHolidayDTO> loadAllPublicHoliday(String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		ArrayList<PublicHolidayDTO>  items = new ArrayList<PublicHolidayDTO>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				PublicHolidayDTO onePublicHoliday = createonePublicHoliday();
				   onePublicHoliday.setPublicHolidayID(result.getInt("PublicHolidayID")); 
	               onePublicHoliday.setName(result.getString("Name")); 
	               onePublicHoliday.setDate(result.getDate("Date")); 
	              			items.add(onePublicHoliday);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}
	@Override
	public void addPublicHoliday(PublicHolidayDTO holiday) throws DAOException {
		
			
			String insertSql = "INSERT INTO publicholiday(Name,Date) VALUES('" +
					 holiday.getName() + "', '"
					+ holiday.getDate() + "');";
			try 
				{
				Class.forName("com.mysql.jdbc.Driver");
				} 
				
			catch (ClassNotFoundException e1) 
				{
				// TODO Auto-generated catch block
				e1.printStackTrace();
				}
			Connection conn = null;
			
			try {
				conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
				Statement st = conn.createStatement();
				Logger.getLogger(getClass().getName()).log(Level.INFO,
						"Executing insert: " + insertSql);
				@SuppressWarnings("unused")
				int res = st.executeUpdate(insertSql);
				st.close();
				} 
			
			catch (Exception e) 
				{
				String error = "Error selecting users. Nested Exception is: " + e;
				Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
				throw new DAOException(error);
				} 
			finally 
			{
				try 
					{
					conn.rollback();
					conn.close();
					} 
				catch (Exception e) 
					{
					}
			}
		}
		public void updatePublicHoliday(PublicHolidayDTO holiday) throws DAOException {
		
			PublicHolidayDTO oldholiday = findPublicHolidayByID(holiday.getPublicHolidayID());
			String updateSql = "";
			if(oldholiday.getName()!=holiday.getName()&& oldholiday.getDate()!=holiday.getDate()){
				 updateSql = "UPDATE publicholiday SET Name = '"
				+ holiday.getName()+ "'," +"Date='"+holiday.getDate()+ "';";
				}
			else if(oldholiday.getDate()!=holiday.getDate()){
				 updateSql = "UPDATE publicholiday SET Date = '"
				+ holiday.getDate()+ "';";
				}
			else{
				 updateSql = "UPDATE publicholiday SET Name = '"
					+ holiday.getName()+ "';";
			}
			
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);			
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {				
				conn.rollback();
				conn.close();				
			} catch (Exception e) {
		}
		}	
	}
		public PublicHolidayDTO findPublicHolidayByID(int Id) throws DAOException {
			String selectSql = "SELECT * FROM publicholiday WHERE PublicHolidayID='" + Id
					+ "';";
			PublicHolidayDTO onePublicHoliday = loadPublicHoliday(selectSql);
			System.out.println(onePublicHoliday.toString());
			return onePublicHoliday;
		}
		
		@Override
		public void deleteHoliday(PublicHolidayDTO holiday) throws DAOException {
			
			String deleteSql = "DELETE FROM publicholiday WHERE (PublicHolidayID ="
				+ holiday.getPublicHolidayID() +"); ";
			
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + deleteSql);
			int res = st.executeUpdate(deleteSql);			
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {				
				conn.rollback();
				conn.close();				
			} catch (Exception e) {
		}
		}
			
		}
		
		
	
	
	
}
